package org.amuthu;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * Class which interacts with the EOD database.
 * Currently the mysql database is used to store the EOD data.
 * Creates a new connection with the mysql database and uses it
 * for insertion and updation of data.
 *
 * Each symbol EOD data is stored in separate table.
 * Separate <B>info</B> table is used to store the last updated
 * date.
 * @author prabhu
 */
public class EodDatabase
{
   private Connection connection = null;
   private boolean checkedInfoTable = false;
   private String databaseName;
   private String host;
   private String port;
   private String username;
   private String password;
   private HashMap<String, Integer> registry = new HashMap<String, Integer>();
   private final static String DATE_FIELD = "date";
   private final static String DAY_OPEN_FIELD = "open";
   private final static String DAY_CLOSE_FIELD = "close";
   private final static String DAY_HIGH_FIELD = "high";
   private final static String DAY_LOW_FIELD = "low";
   private final static String DAY_VOLUME_FIELD = "volume";

   /**
    * Creates a new quote source to connect to an external database.
    *
    * @param dbName the name of the database
    * @param host the host location of the database
    * @param port the port of the database
    * @param username the user login
    * @param password the password for the login
    */
   public EodDatabase(String dbName, String host, String port,
                      String username, String password)
   {
      this.host = host;
      this.port = port;
      this.username = username;
      this.password = password;
      this.databaseName = dbName;

      if (checkConnection())
      {
         loadSymbols();
      }
   }

   private void loadSymbols()
   {
      try
      {
         Statement statement = connection.createStatement();
         ResultSet result = statement.executeQuery("show tables from " + databaseName + " like 'Table_%'");

         while (result.next())
         {
//              System.out.println(result.getString(1));
            registry.put(toOrigSymbolStr(result.getString(1)), 1);
         }
      }
      catch (SQLException ex)
      {
         Logger.getLogger(EodDatabase.class.getName()).log(Level.SEVERE, null, ex);
      }
   }

   private boolean isSymbolTablePresent(String sym)
   {
      return registry.containsKey(sym);
   }

   public boolean getSymbols(ArrayList<String> symbols)
   {
      boolean success = true;

      do
      {
         if (symbols == null)
         {
            break;
         }

         try
         {
            Statement statement = connection.createStatement();
            ResultSet result = statement.executeQuery("show tables from " + databaseName + " like 'Table_%'");

            while (result.next())
            {
               symbols.add(toOrigSymbolStr(result.getString(1)));
            }
         }
         catch (SQLException ex)
         {
            success = false;
            Logger.getLogger(EodDatabase.class.getName()).log(Level.SEVERE, null, ex);
         }
         break;
      }
      while (true);

      return success;
   }

   public boolean checkNoOfDays(String symbol, TradingDate lastDate, int nbDays)
   {
      boolean success = false;
      int entryCount = 0;
      String query = "select count(date) from " + symbol + " where date <= ?";

      try
      {
         PreparedStatement statement = connection.prepareStatement(query);
         statement.setString(1, lastDate.toString("yyyy-mm-dd"));

         ResultSet result = statement.executeQuery();

         while (result.next())
         {
            entryCount = result.getInt(1);
         }

         if (entryCount >= nbDays)
         {
            success = true;
         }

         result.close();
         statement.close();
      }
      catch (SQLException ex)
      {
         Logger.getLogger(EodDatabase.class.getName()).log(Level.SEVERE, null, ex);
      }

      return success;
   }

   public boolean loadQuote(String symbol, TradingDate lastDate, int nbDays)
   {
      boolean success = false;
      String query = "select * from " + symbol + " where date <= ? order by date desc limit ?";

      try
      {
         PreparedStatement statement = connection.prepareStatement(query);
         statement.setString(1, lastDate.toString("yyyy-mm-dd"));
         statement.setInt(2, nbDays);

         ResultSet result = statement.executeQuery();

         double high[] = new double[nbDays];
         double low[] = new double[nbDays];
         double open[] = new double[nbDays];
         double close[] = new double[nbDays];
         double volume[] = new double[nbDays];

         for (int i = nbDays - 1; result.next() && i >= 0; i--)
         {
            open[i] = result.getDouble(2);
            close[i] = result.getDouble(3);
            high[i] = result.getDouble(4);
            low[i] = result.getDouble(5);
            volume[i] = result.getInt(6);

            //System.out.println(volume[i]);
         }

         QuoteDataStore dataStore = QuoteDataStore.getInstance();

         dataStore.setOpen(open);
         dataStore.setClose(close);
         dataStore.setHigh(high);
         dataStore.setLow(low);
         dataStore.setVolume(volume);

         dataStore.setSize(nbDays);

         result.close();
         statement.close();

         success = true;
      }
      catch (SQLException ex)
      {
         Logger.getLogger(EodDatabase.class.getName()).log(Level.SEVERE, null, ex);
      }

      return success;
   }

   public boolean checkGlobalFilter(String symbol, TradingDate lastDate, GlobalFilter globalFilter)
   {
      boolean success = false;

      if (globalFilter.getAvgDays() <= 0 || (globalFilter.getAvgPrice() <= 0.0 && globalFilter.getAvgVolume() <= 0.0))
      {
         return true;
      }

      String query = "select * from (select * from " + symbol + " where date <= ? order by date desc limit ?) as ta having ";

      if (globalFilter.getAvgVolume() > 0.0)
      {
         query += " avg(volume) > " + globalFilter.getAvgVolume();
      }

      if (globalFilter.getAvgPrice() > 0.0)
      {
         if (globalFilter.getAvgVolume() > 0.0)
         {
            query += " and ";
         }

         query += " avg(close) > " + globalFilter.getAvgPrice();
      }

      try
      {
         PreparedStatement statement = connection.prepareStatement(query);

         statement.setString(1, lastDate.toString("yyyy-mm-dd"));
         statement.setInt(2, globalFilter.getAvgDays());

         ResultSet result  = statement.executeQuery();

         while(result.next())
         {
            success = true;
         }

         result.close();
         statement.close();
      }
      catch (SQLException ex)
      {
         Logger.getLogger(EodDatabase.class.getName()).log(Level.SEVERE, null, ex);
      }

      return success;
   }

   public boolean loadQuote(String symbol, TradingDate lastDate, GlobalFilter globalFilter, int nbDays)
   {
      boolean success = false;

      do
      {
         if (!isSymbolTablePresent(symbol))
         {
            break;
         }

         String dbSymbol = toDbSymbolStr(symbol);

         if (!checkNoOfDays(dbSymbol, lastDate, nbDays))
         {
            //System.out.println("Number of days is less than required days for " + dbSymbol);
            break;
         }

         if (globalFilter != null && !checkGlobalFilter(dbSymbol, lastDate, globalFilter))
         {
            //System.out.println("Global filter is not satisfied  for " + dbSymbol);
            break;
         }

         if (!loadQuote(dbSymbol, lastDate, nbDays))
         {
            System.out.println("Loading quote failed for " + dbSymbol);
            break;
         }

         success = true;
         break;
      }
      while (true);

      return success;
   }

   public boolean loadQuote(String symbol, TradingDate lastDate, double avgPrice, double avgVolume, int avgDays, int nbDays)
   {
      boolean success = true;

      do
      {
         if (!isSymbolTablePresent(symbol))
         {
            success = false;
            break;
         }

         String dbSymbol = toDbSymbolStr(symbol);

         try
         {
            Statement statement = connection.createStatement();
            String query = "SELECT ";

            if (lastDate != null)
            {
               query += " max(date) ,";
            }

            query += " count(date) from " + dbSymbol + " having count(date) > " + nbDays;

            if (lastDate != null)
            {
               query += " and max(date)  = '" + lastDate.toString("yyyy-mm-dd" + "'");
            }

            if ((avgVolume > 0 || avgPrice > 0) && avgDays > 1)
            {
               query += " and (select avg(totalvol) from (select volume as totalvol, close as price from " + dbSymbol + " order by date desc limit " + avgDays;
               query += " ) as tb having";

               if (avgVolume > 0)
               {
                  query += " avg(totalvol) > " + avgVolume;
               }

               if (avgPrice > 0)
               {
                  if (avgVolume > 0)
                  {
                     query += " and ";
                  }
                  query += " avg(price)  > " + avgPrice;
               }

               query += " )";
            }
            ResultSet result = statement.executeQuery(query);

            if (result.next())
            {
               success = loadQuote(symbol, nbDays);
            }
            else
            {
               success = false;
            }
         }
         catch (SQLException ex)
         {
            success = false;
            Logger.getLogger(EodDatabase.class.getName()).log(Level.SEVERE, null, ex);
         }
         break;
      }
      while (true);

      return success;
   }

   public boolean loadQuote(String symbol, int nbDays) throws SQLException
   {
      if (!isSymbolTablePresent(symbol))
      {
         return false;
      }

      String dbSymbol = toDbSymbolStr(symbol);

      try
      {
         Statement statement = connection.createStatement();
         ResultSet result = statement.executeQuery("SELECT count(*) FROM " + dbSymbol);

         int entryCount = 0;

         while (result.next())
         {
            entryCount = result.getInt(1);
         }

         if (nbDays > entryCount)
         {
            nbDays = entryCount;
         }

         double high[] = new double[nbDays];
         double low[] = new double[nbDays];
         double open[] = new double[nbDays];
         double close[] = new double[nbDays];
         double volume[] = new double[nbDays];

         String query = "SELECT * FROM " + dbSymbol + " ORDER BY date DESC LIMIT " + nbDays;
         result = statement.executeQuery(query);

         for (int i = nbDays - 1; result.next() && i >= 0; i--)
         {
            open[i] = result.getDouble(2);
            close[i] = result.getDouble(3);
            high[i] = result.getDouble(4);
            low[i] = result.getDouble(5);
            volume[i] = result.getInt(6);
         }

         QuoteDataStore dataStore = QuoteDataStore.getInstance();

         dataStore.setOpen(open);
         dataStore.setClose(close);
         dataStore.setHigh(high);
         dataStore.setLow(low);
         dataStore.setVolume(volume);

         dataStore.setSize(nbDays);

         return true;
      }
      catch (Exception e)
      {
         System.err.println("Error connecting to database driver " + e.getMessage());
      }
      return false;
   }

   private boolean checkConnection()
   {
      boolean success = true;

      // Connect to database if we haven't already
      if (connection == null)
      {
         // Connect
         success = connect();
      }

      // If we are connected, check the tables exist, if not, create them.
      if (connection != null && !checkedInfoTable)
      {
         checkedInfoTable = checkDatabase();
         success = checkedInfoTable;
      }

      return success;
   }

   private boolean checkDatabase()
   {
      boolean success = true;

      try
      {
         DatabaseMetaData meta = connection.getMetaData();
         {
            ResultSet RS = meta.getCatalogs();
            boolean foundDatabase = false;

            while (RS.next())
            {
               if (RS.getString(1).equals(databaseName))
               {
                  foundDatabase = true;
                  break;
               }
            }

            if (!foundDatabase)
            {
               System.err.println("Can't find database");
               return false;
            }
         }
      }
      catch (SQLException e)
      {
         System.err.println("Error connecting to database driver " + e.getMessage());

         return false;
      }

      return success;
   }

   private String toDbSymbolStr(String symbol)
   {
      String dbSymbol = symbol;

      dbSymbol = dbSymbol.replaceAll("&", "046");
      dbSymbol = dbSymbol.replaceAll("-", "055");

      if (Character.isDigit(dbSymbol.charAt(0)))
      {
         dbSymbol = "_" + dbSymbol;
      }

      dbSymbol = "Table_" + dbSymbol;

      return dbSymbol;
   }

   private String toOrigSymbolStr(String symbol)
   {
      String dbSymbol = symbol;

      dbSymbol = dbSymbol.substring(6);
      dbSymbol = dbSymbol.replaceAll("046", "&");
      dbSymbol = dbSymbol.replaceAll("055", "-");

      if (dbSymbol.charAt(0) == '_')
      {
         dbSymbol = dbSymbol.substring(1);
      }

      return dbSymbol;
   }

   private boolean connect()
   {
      try
      {
         // Resolve the classname
         //Class.forName("org.sqlite.JDBC");
         //String connectionURL = new String("jdbc:sqlite:" + databaseName);

         Class.forName("com.mysql.jdbc.Driver");
         String connectionURL = "jdbc:mysql://" + host + ":" + port + "/" + databaseName;

         if (username != null)
         {
            connectionURL += "?user=" + username + "&password=" + password;
         }

         connection = DriverManager.getConnection(connectionURL);
      }
      catch (ClassNotFoundException e)
      {
         System.err.println("Unable to load database driver " + e.getMessage());
         return false;
      }
      catch (SQLException e)
      {
         System.err.println("Error connecting to database driver " + e.getMessage());
         return false;
      }

      return true;
   }

}
